using System;
using System.Data;
using System.Configuration;

using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;

using StillVoiceCommon;
using TN5Y.Server;
using TN5Y;
using Microsoft.ApplicationBlocks.Data;
using System.Data.SqlClient;
using System.Text;
using System.Collections.Generic;
using System.IO;

namespace TeamTime.Classes
{
   public class Person : DataTableRow
   {

       
       //public string CompanyName { get; set; }
       public string FullNameAndID { get; set; }
       public Person()
       {
           this.initClass(Utilities.DBConnectionString, "Person");
       }
       public new void load(Int32 id)
       {
           base.load(id);
           System.Data.SqlClient.SqlDataReader dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text,
                "select * from ViewPersons where ID = " + id);

           if (dr.Read())
           {
               UserNameModifiedBy = dr["UserNameModifiedBy"].ToString();
               //CompanyName = dr["CompanyName"].ToString();
               FullNameAndID = dr["FullNameAndID"].ToString();
           }
           dr.Close();
           dr = null;
       }

       internal Person loadByIDNum(string IdentityNumber, Int32 CompanyID)
       {
           Int32 ID;
           ID = Convert.ToInt32(SqlHelper.ExecuteScalar(TN5Y.Utilities.DBConnectionString, CommandType.Text,
               "select ID from Person where IDNumber = @IdentityNumber and CompanyID = @CompanyID", new SqlParameter("IdentityNumber",
               IdentityNumber), new SqlParameter("CompanyID", CompanyID)));
           base.load(ID);
           return this;
       }

       internal Int32 AddToCompany(int CompanyID)
       {
           string sql = "if not exists(select ID from CompanyPerson where PersonID = @PersonID and CompanyID = @CompanyID)" +
                        " insert into CompanyPerson (PersonID, CompanyID ) values (@PersonID, @CompanyID )";
           SqlHelper.ExecuteNonQuery(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql, 
                                    new SqlParameter("PersonID", Convert.ToInt32( this.KeyValue)),
                                    new SqlParameter("CompanyID", CompanyID));
           sql = "select ID from CompanyPerson where PersonID = @PersonID and CompanyID = @CompanyID";
           return Convert.ToInt32(SqlHelper.ExecuteScalar(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql,
                                     new SqlParameter("PersonID", Convert.ToInt32(this.KeyValue)),
                                     new SqlParameter("CompanyID", CompanyID)));
       }
       internal void RemoveFromCompany(int CompanyID)
       {
           string sql = "delete from CompanyPerson where PersonID = @PersonID and CompanyID = @CompanyID";
           SqlHelper.ExecuteNonQuery(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql,
                                    new SqlParameter("PersonID", Convert.ToInt32(this.KeyValue)),
                                    new SqlParameter("CompanyID", CompanyID));
       }

       internal List<Company> GetCompanies()
       {
           SqlDataReader dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text,
                "select * from ViewCompanies c inner join CompanyPerson cp on c.ID = cp.CompanyID where cp.PersonID = @PersonID order by CompanyName",
                new SqlParameter("PersonID", Convert.ToInt32(this.KeyValue)));
           List<Company> l = new List<Company>();

           while (dr.Read())
           {
               Company company = new Company();
               company.load(Convert.ToInt32(dr["ID"]));
               l.Add(company);

           }
           return l;
       }
   }

   public class Persons
   {
       
       public DataSet searchPersons(string searchFor, string companyName, bool searchInNotes, 
                    string sortExpression, Int16 officeID) 
       {
           StringBuilder sql = new StringBuilder("SELECT distinct p.* FROM ViewPersons p ");
            if (officeID > 0)
           {
                sql.Append(" left join (select cp.PersonID, p.OfficeID	from CompanyPerson cp 	" );
                sql.Append(" inner join Company c on cp.CompanyID = c.ID	");
                sql.Append(" inner join Province p on c.ProvinceID = p.ID ) as t1 on p.ID = t1.PersonID ");
           }
           sql.Append (" where 1=1 ");
           if (searchFor != "") //Search for surname, first name and ID
           {
               sql.Append(" and (FirstName like '%'+@SearchFor+'%' or");
               sql.Append(" Surname like '%'+@SearchFor+'%' or");
               sql.Append(" FullName like '%'+@SearchFor+'%' or");
               sql.Append(" IDNumber like '%'+@SearchFor+'%' or");
               sql.Append(" p.ID like '%'+@SearchFor+'%' ");
               //if (searchInNotes)
               //{
               //    sql.Append(" or");
               //    sql.Append(" Notes like '%");
               //    sql.Append(searchFor);
               //    sql.Append("%'");
               //}
               sql.Append(") ");
           }

           if (companyName != "")
           {
               sql.Append("and CompanyName like '%");
               sql.Append(companyName);
               sql.Append("%'");
           }
           if (officeID > 0)
           {
               sql.Append(" and (t1.OfficeID = @OfficeID or t1.OfficeID is null) ");
           }
           if (sortExpression != "")
           {
               sql.Append(" order by " + sortExpression);
           }
           return SqlHelper.ExecuteDataset(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql.ToString(),
               new SqlParameter("SearchFor", searchFor),
               new SqlParameter("OfficeID", officeID));

       }

       public bool duplicatePersonExists(string PersonIDNumber, string ID)
       {
           bool ret = false;
           string sql = "select ID from ViewPersons where IDNumber = '" + PersonIDNumber + "' ";
           if (ID != "") { sql += " and ID <> '" + ID + "'"; }//same name different id
           SqlDataReader dr;
           dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql);
           ret = dr.Read();
           dr.Close();
           return ret;
       }

       public int getDuplicatePersonID(string PersonIDNumber, string ID)
       {
           int ret = 0;
           string sql = "select ID from ViewPersons where IDNumber = '" + PersonIDNumber + "' ";
           if (ID != "") { sql += " and ID <> '" + ID + "'"; }//same name different id
           SqlDataReader dr;
           dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql);
           if (dr.Read())
           {
               ret = Convert.ToInt32(dr[0]);
           }
           dr.Close();
           return ret;
       }

       internal string GetPersonEmail(Int32 PersonID)
       {
           string ret = "";
           string sql = "select Email from ViewPersons where ID = @PersonID";

           SqlDataReader dr;
           dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql,
               new SqlParameter("PersonID", PersonID));
           if (dr.Read())
           {
               ret = dr[0].ToString();
           }
           dr.Close();
           return ret;
       }

       internal int GetPersonIDFromNameAndID(string NameAndID)
       {
           int ret = 0;
           NameAndID = NameAndID.Trim();
           string sql = "select ID from ViewPersons where FullNameAndID = @NameAndID"; //+'%'

           SqlDataReader dr;
           dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql,
               new SqlParameter("NameAndID", NameAndID));
           if (dr.Read())
           {
               ret = Convert.ToInt32(dr[0]);
           }
           dr.Close();
           return ret;
       }



       internal string getPersonFullNameAndIDNumFromID(Int32 personID)
       {
           string sql = "select FullNameAndID from ViewPersons where ID = @PersonID";
           return SqlHelper.ExecuteScalar(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql,
                           new SqlParameter("PersonID", personID)).ToString();
       }
   }
}





/*
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using StillVoiceCommon;
using System.Text;
using System.IO;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;
using System.Collections.Generic;

/// <summary>
/// Summary description for Person
/// </summary>

namespace TeamTime.Classes
{

    public class Person : StillVoiceCommon.DataTableRow
    {
        #region properties
        private bool isOwner;
        public bool IsOwner
        {
            get { return isOwner; }
            set { isOwner = value; }
        }

        private bool isMaint;
        public bool IsMaintenancePerson
        {
            get { return isMaint; }
            set { isMaint = value; }
        }

        private bool isDeliv;
        public bool IsDeliveryPerson
        {
            get { return isDeliv; }
            set { isDeliv = value; }
        }

        private string usernameModifiedBy;

        public new string UserNameModifiedBy
        {
            get { return usernameModifiedBy; }
            set { usernameModifiedBy = value; }
        }
        #endregion

        public Person()
        {
            this.initClass(TN5Y.Utilities.DBConnectionString, "Person");
        }
        public new void load(string id)
        {
            base.load(id);
            System.Data.SqlClient.SqlDataReader dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text, "select * from ViewPersons where ID = " + id);
            //dr = StillVoiceCommon.sqlDB.getDR("select * from ViewPersons where ID = " + id);
            if (dr.Read())
            {
                usernameModifiedBy = dr["UserNameModifiedBy"].ToString();
            }
            dr.Close();
            dr = null;
        }
    }

    public class Persons
    {
        public DataSet getPersonServices(bool anyOption)
        {
            string sql = "select ID, Description  from PersonService";
            if (anyOption) sql = "select 0 as ID,'<Any>' as Description UNION " + sql;
            return SqlHelper.ExecuteDataset(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql);
        }

        public DataSet searchPersons(string searchFor, bool searchInNotes, int personServiceID, string sortExpression) //, string fromDate, string toDate, 
        {
            StringBuilder sql = new StringBuilder("SELECT * FROM ViewPersons where 1=1 ");
            if (searchFor != "")
            {
                sql.Append(" and (FirstName like '%");
                sql.Append(searchFor);
                sql.Append("%' or");
                sql.Append(" Surname like '%");
                sql.Append(searchFor);
                sql.Append("%' or");
                sql.Append(" ID like '%");
                sql.Append(searchFor);
                sql.Append("%'");
                if (searchInNotes)
                {
                    sql.Append(" or");
                    sql.Append(" Notes like '%");
                    sql.Append(searchFor);
                    sql.Append("%'");
                }
                sql.Append(") ");
            }
            

            if (sortExpression != "")
            {
                sql.Append(" order by " + sortExpression);
            }
            return sqlDB.getDS(sql.ToString());
        }



        public bool duplicatePersonExists(string PersonIDNumber, string ID)
        {
            bool ret = false;
            string sql = "select ID from ViewPersons where IDNumber = '" + PersonIDNumber + "' ";
            if (ID != "") { sql += " and ID <> '" + ID + "'"; } //<- ??
            SqlDataReader dr;
            dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql);
            ret = dr.Read();
            dr.Close();
            return ret;
        }

        public int getDuplicatePersonID(string PersonIDNumber, string ID)
        {
            int ret = 0;
            string sql = "select ID from ViewPersons where IDNumber = '" + PersonIDNumber + "' ";
            if (ID != "") { sql += " and ID <> '" + ID + "'"; }
            SqlDataReader dr;
            dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql);
            if (dr.Read())
            {
                ret = Convert.ToInt32(dr[0]);
            }
            dr.Close();
            return ret;
        }

        internal string GetPersonEmail(Int32 PersonID)
        {
            string ret = "";
            string sql = "select EmailAddress from ViewPersons where ID = @PersonID";

            SqlDataReader dr;
            dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql, 
                new SqlParameter("PersonID", PersonID));
            if (dr.Read())
            {
                ret = dr[0].ToString();
            }
            dr.Close();
            return ret;
        }

    }
}*/
